/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.daos;

import com.insurance.cis.connection.CisConnection;
import com.insurance.cis.models.Product;
import com.insurance.cis.models.ProductCategory;
import com.insurance.cis.scos.ProductSearchCondition;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TruongLQ
 */
public class ProductDao {

    public List<Product> getProducts(ProductSearchCondition sco) {
        List<Product> products = new ArrayList<>();
        ResultSet rs;
        final StringBuilder sql = new StringBuilder();
        sql.append("select distinct ");
        sql.append("prod.ProductID as ProductID,"); 
        sql.append("prod.ProductName as ProductName,");
        sql.append("prod.ProductDesc as ProductDesc, ");
        sql.append("prodCat.ProductCatID as ProductCatID, ");
        sql.append("prodCat.ProductCatDesc as ProductCatDesc ");

        sql.append("from Product prod, ProductCategory prodCat ");
        sql.append("where prod.ProductCatID=prodCat.ProductCatID");
        try {
            if (sco == null || sco.isEmptyCondition()) {
                sql.append(" order by prod.ProductID asc");
                Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
                rs = st.executeQuery(sql.toString());
            } else {
                Map<Integer, Object> parameters = new HashMap<>();
                int count = 0;
                if (sco.getId()!=null&&!sco.getId().isEmpty()) {
                    sql.append(" and prod.ProductID = ? ");
                    parameters.put(++count, sco.getId());
                }
                if (sco.getName()!=null&&!sco.getName().isEmpty()) {
                    sql.append(" and prod.ProductName like ?");
                    parameters.put(++count, "%" + sco.getName() + "%");
                }

                if (sco.getCategoryId() != null) {
                    sql.append(" and prod.ProductCatID = ?");
                    parameters.put(++count, sco.getCategoryId());
                }
                
                if (sco.getProductDesc()!=null && !sco.getProductDesc().isEmpty()) {
                    sql.append(" and prod.ProductDesc like ?");
                    parameters.put(++count, "%"+sco.getProductDesc()+"%");
                }
                
                sql.append(" order by prod.ProductID asc");
                PreparedStatement pst = CisConnection.getInstance()
                        .getSqlConnection().prepareStatement(sql.toString());
                for (Integer pos : parameters.keySet()) {
                    pst.setObject(pos, parameters.get(pos));
                }

                rs = pst.executeQuery();
            }
            buildProducts(products, rs);
            return products;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
            return products;
        }
    }

    private void buildProducts(List<Product> products, ResultSet rs) throws SQLException {
        while (rs.next()) {
            products.add(new Product(rs.getString("ProductID"),
                    rs.getString("ProductName"), rs.getString("ProductDesc"),
                    new ProductCategory(rs.getInt("ProductCatID"), rs.getString("ProductCatDesc"))));
        }
    }
    
    public boolean saveProduct(Product product) {
        final String SQL = "insert into Product values (?,?,?,?)";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, product.getId());
            pst.setInt(2, product.getCategory().getId());
            pst.setString(3, product.getName());
            pst.setString(4, product.getDesc());
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean checkIDExist(String productID) {
        final String SQL = "select * from Product where ProductID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, productID);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean checkProductNameExist(String productName) {
        final String SQL = "select * from Product where ProductName=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, productName);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public Product getByID(String productID) {
        try {
            final String SQL  = "select * from Product where ProductID=?";
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, productID);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                final String SQL_Cat  = "select * from ProductCategory where ProductCatID=?";
                PreparedStatement pstCat = CisConnection.getInstance().getSqlConnection()
                        .prepareStatement(SQL_Cat);
                pstCat.setInt(1, rs.getInt("ProductCatID"));
                ResultSet rsCat = pstCat.executeQuery();
                rsCat.next();
                Product product = new Product(
                        rs.getString("ProductID"),
                        rs.getString("ProductName"),
                        rs.getString("ProductDesc"), new ProductCategory(
                        rsCat.getInt("ProductCatID"), rsCat.getString("ProductCatDesc")));
                return product;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public boolean updateProduct(Product product) {
        final String SQL  = "select * from Product where ProductID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, product.getId());
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                rs.updateInt(2, product.getCategory().getId());
                rs.updateString(3, product.getName());
                rs.updateString(4, product.getDesc());
                rs.updateRow();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean deleteProduct(String productID) {
        final String SQL  = "delete from Product where ProductID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, productID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
